/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package autodiaryserver;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author kkkkkkkkkkkk
 * 
 */
public class DataPackager {

    /*
     * Function: fill in buffer tables bufSize records every timeInterval
     * inputs:
     * - bufSize:       number of records will be put in the buffer
     * - timeInterval:  the interval this function repeats. 0 for fastest THIS FUNCTION IS NOT IMPLEMENTED YET
     * - fromTimeInt:   return the data after fromTimeInt, if fromTimeInt < 0 , return the lastest one
     * - toTimeInt:     return the data before toTimeInt only, if fromTimeInt < 0, toTimeInt will be ignored
     * output: 
     * - return number of records filled in buffer
     *      Return -1 if there are any error
     * 
     * NOTE: precise of the data is time in second. It means if there are more than one samples
     * in a second, only one sample in that second will be put in the buffer.
     */
    public static int fillBuffer(int bufSize, int fromTimeInt, int toTimeInt, int timeInterval){

        int total = 0;

        total = fillPrimaryBufferTable(Declare.GPS_DATA_BUFFER_TABLE_NAME, bufSize, fromTimeInt, toTimeInt, false);
        total += fillPrimaryBufferTable(Declare.PRIMITIVE_ACTIVITY_BUFFER_TABLE_NAME, bufSize, fromTimeInt, toTimeInt, true);
        total += fillSecondaryBufferTables();
    
        // and set buffer started is true
        //

        
        return total;
    }
    
    // fill in the buffer table with data taken from secondary tables which are d_address, d_places, d_placesTypes and d_actGpsPlace
    public static int fillSecondaryBufferTables(){
        int count = 0;
        String sqlCommand = "";
        
        // clear secondary buffer tables
        //clearBuffer( Declare.ADDRESS_BUFFER_TABLE_NAME );
        clearBuffer( Declare.PLACES_BUFFER_TABLE_NAME );
        //clearBuffer( Declare.PLACES_TYPES_BUFFER_TABLE_NAME );
        //clearBuffer( Declare.ACT_GPS_PLACE_BUFFER_TABLE_NAME );

        try {
            
            Statement stm = null;
            
            // fill buffer address
            //stm = Declare.jdbcCon.createStatement();
            //sqlCommand = "INSERT INTO d_buf_address(id, formatted_address, street_number, route, locality, administrative_area_level_1, country, postal_code, gpsData_id, place_id) "
            //        + "SELECT b.id, b.formatted_address, b.street_number, b.route, b.locality, b.administrative_area_level_1, b.country, b.postal_code, b.gpsData_id, b.place_id "
            //        + "FROM d_buf_gpsData a, d_address b "
            //        + "WHERE a.id = b.gpsData_id ";
            //count += stm.executeUpdate(sqlCommand);
            //stm.close();

            // fill buffer places
            stm = Declare.jdbcCon.createStatement();
            sqlCommand = "INSERT INTO d_buf_places(id, name, distance, gpsData_id, latitude, longitude) "
                    + "SELECT a.id, a.name, a.distance, a.gpsData_id, a.latitude, a.longitude "
                    + "FROM d_buf_gpsData b, d_places a "
                    + "WHERE b.id = a.gpsData_id ";
            count += stm.executeUpdate(sqlCommand);
            stm.close();

            // fill buffer places address
            //stm = Declare.jdbcCon.createStatement();            
            //sqlCommand = "INSERT INTO d_buf_address(id, formatted_address, street_number, route, locality, administrative_area_level_1, country, postal_code, gpsData_id, place_id) "
            //        + "SELECT b.id, b.formatted_address, b.street_number, b.route, b.locality, b.administrative_area_level_1, b.country, b.postal_code, b.gpsData_id, b.place_id "
            //        + "FROM d_buf_places a, d_address b "
            //        + "WHERE a.id = b.place_id and b.gpsData_id IS NULL ";
            //count += stm.executeUpdate(sqlCommand);
            //stm.close();

            //stm = Declare.jdbcCon.createStatement();
            //sqlCommand = "INSERT INTO d_buf_placesTypes(place_id, type_id)"
            //        + " SELECT a.place_id, a.type_id"
            //        + " FROM d_buf_places b, d_placesTypes a"
            //        + " WHERE b.id = a.place_id";
            //count += stm.executeUpdate(sqlCommand);
            //stm.close();

        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return count;
    }
    
    // fill in the buffer with data from primary tables which are: d_gpsData, d_primActivities
    // return the number of records buffered
    // after fill last buffer table will mark bufferStarted = true in registry
    public static int fillPrimaryBufferTable(String tableName, int bufSize, int fromTimeInt, int toTimeInt, boolean lastTable){
        
        // get the latest data in the buffer table, 0 if empty
        int latestTimeInt = getMaxTimeIntInBuffer(tableName.trim());
        // clear buffer
        clearBuffer( tableName.trim() );
        
        // if reach to toTimeInt (end of range)
        if ( latestTimeInt >= toTimeInt ) { return 0; }
        
        // total records filled
        int count = 0;
        
        PreparedStatement tempStm = null;
        
        // get data from a time range
        if ( fromTimeInt >= 0 ) {
            // which table
            tempStm = getStatementTimeRange(tableName);
            
            // if can't find a table, return -1
            if ( tempStm == null ) { return -1; }

            try {
                // if in repeat
                if ( latestTimeInt > fromTimeInt ){ 
                    tempStm.setInt(1, latestTimeInt + 1);
                } else{ 
                    // check if at the begining of the time range
                    // then start buffering and register to registry pair:
                    // name = buffer_started, value = true
                    // need to manually set value = false before runing this procedure 
                    // if buffer started, it mean buffer recirle again then do nothing
                    // if buffer wasn't started, it means it is first time to run then run it.
                    if ( bufferStarted() ){
                        return 0;
                    }else{
                        // if not started yet then start buffer
                        tempStm.setInt(1, fromTimeInt);
                        // mark buffer started = true -> not repeat buffer whole the time range again
                        if ( lastTable ){ reigstryBufferStarted(); }    
                    }
                    
                }

                tempStm.setInt(2, toTimeInt);
                tempStm.setInt(3, bufSize);
                count = tempStm.executeUpdate();

            //--------------------------------------------------------------
            //System.out.println("Prepare statement: " + tempStm.toString());
            //--------------------------------------------------------------
                
            } catch (SQLException ex) {
                Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        // get latest data
        if ( fromTimeInt < 0 ) {
            // which table
            tempStm = getStatementLatestData(tableName);
            
            // if can't find a table, return -1
            if ( tempStm == null ) { return -1; }
            
            try {
                tempStm.setInt(1, bufSize);
                count = tempStm.executeUpdate();                
            } catch (SQLException ex) {
                Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        try {
            tempStm.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return count;
    }
    
    public static void reigstryBufferStarted(){
        registry(Declare.REG_NAME_BUFFER_STARTED, Declare.REG_VALUE_BUFFER_STARTED_TRUE);
    }
    
    /*
     * check registry table if bufferStarted = true then return true
     * else return false
     * if can't find the pair then register the pair with value = false and return false
     */
    public static boolean bufferStarted(){
        boolean result = true;

        try {

            
            PreparedStatement ps = Declare.jdbcCon.prepareStatement("SELECT name, value FROM d_registry WHERE name = ?");
            ps.setString(1, Declare.REG_NAME_BUFFER_STARTED);
            ResultSet rs = ps.executeQuery();
            
            if ( rs.first() ){
                // get value
                String regValue = rs.getString(2);
                // return in boolean 
                if ( regValue.trim().equalsIgnoreCase(Declare.REG_VALUE_BUFFER_STARTED_TRUE) ){
                    result = true;
                }else{
                    result = false;
                }
            }else{
                // do not have in registry -> not started yet
                // -> insert new pair to registry with value is false
                registry(Declare.REG_NAME_BUFFER_STARTED, Declare.REG_VALUE_BUFFER_STARTED_FALSE);
                // return boolean value
                result = false;
            }
            
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;        
    }
    
    public static void clearRegistry(String name){
        try {
            
            PreparedStatement ps = Declare.jdbcCon.prepareStatement("DELETE FROM d_registry WHERE name = ?");
            ps.setString(1, name);
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }
    
    public static boolean registry(String name, String value){
        boolean result = false;
        
        try {
            
            // check if the pair exist
            PreparedStatement ps = Declare.jdbcCon.prepareStatement("SELECT name, value FROM d_registry WHERE name = ?");
            ps.setString(1, name.trim());
            ResultSet rs = ps.executeQuery();
            
            // if the pair exist -> update value
            if ( rs.first() ){
                PreparedStatement ps2 = Declare.jdbcCon.prepareStatement("UPDATE d_registry SET value = ? WHERE name = ?");
                ps2.setString( 1, value.trim() );
                ps2.setString( 2, name.trim() );
                ps2.executeUpdate();
                result = true;
                ps2.close();
            }else{
                // do not have in registry -> not started yet
                // -> insert new pair to registry with value is false
                PreparedStatement ps2 = Declare.jdbcCon.prepareStatement("INSERT INTO d_registry (name, value) VALUES (?, ?)");
                ps2.setString( 1, name.trim() );
                ps2.setString( 2, value.trim() );
                ps2.executeUpdate();
                // return boolean value
                result = true;
                ps2.close();
            }
            
            ps.close();

        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;        
    }
    
    public static PreparedStatement getStatementTimeRange(String tableName){
        PreparedStatement ps = null;
        
        try {        
            if ( tableName.trim().equalsIgnoreCase(Declare.GPS_DATA_BUFFER_TABLE_NAME) ){
                ps = Declare.jdbcCon.prepareStatement(
                        "INSERT INTO d_buf_gpsData(id, device_id, latitude, longitude, accuracy, speed, time, accuracy_int, speed_int, time_int) "
                        + "SELECT id, device_id, latitude, longitude, accuracy, speed, time, accuracy_int, speed_int, time_int "
                        + "FROM d_gpsData "
                        + "WHERE time_int >= ? and time_int <= ? "
                        + "ORDER BY time_int "
                        + "LIMIT ?");

                return ps;
            }
            if ( tableName.trim().equalsIgnoreCase(Declare.PRIMITIVE_ACTIVITY_BUFFER_TABLE_NAME) ){
                ps = Declare.jdbcCon.prepareStatement(
                        "INSERT INTO d_buf_primActivities(id, device_id, code, time, time_int) "
                        + "SELECT id, device_id, code, time, time_int "
                        + "FROM d_primActivities "
                        + "WHERE time_int >= ? and time_int <= ? "
                        + "ORDER BY time_int "
                        + "LIMIT ? ");
                
                return ps;
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        // if can't find a table
        return null;
    }
    
    public static PreparedStatement getStatementLatestData(String tableName){
        PreparedStatement ps = null;
        
        try {              
            if ( tableName.trim().equalsIgnoreCase(Declare.GPS_DATA_BUFFER_TABLE_NAME) ){
                ps = Declare.jdbcCon.prepareStatement(
                        "INSERT INTO d_buf_gpsData(id, device_id, latitude, longitude, accuracy, speed, time, accuracy_int, speed_int, time_int) "
                        + "SELECT id, device_id, latitude, longitude, accuracy, speed, time, accuracy_int, speed_int, time_int "
                        + "FROM d_gpsData "
                        + "ORDER BY time_int desc "
                        + "LIMIT ?");

                return ps;
            }
            if ( tableName.trim().equalsIgnoreCase(Declare.PRIMITIVE_ACTIVITY_BUFFER_TABLE_NAME) ){
                ps = Declare.jdbcCon.prepareStatement(
                        "INSERT INTO d_buf_primActivities(id, device_id, code, time, time_int) "
                        + "SELECT id, device_id, code, time, time_int "
                        + "FROM d_primActivities "
                        + "ORDER BY time_int desc "
                        + "LIMIT ?");
                
                return ps;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        // if can't find a table
        return null;
    }
    
    
    public static int getMaxTimeIntInBuffer(String tableName){
        int maxTimeInt = 0;
        
        try {
            // get id to insert places aswell
            Statement stm = Declare.jdbcCon.createStatement();
            ResultSet rs = stm.executeQuery("select max(time_int) as max_time_int from " + tableName.trim() + ";");
            
            if (rs.first()){
                maxTimeInt = rs.getInt(1);
            } // else, maxTimeInt = 0 as initiation
            
            rs.close();
            stm.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataPackager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return maxTimeInt;        
    }
    
    public static int clearBuffer(){
        int total = 0;
        // clear primary tables
        total += clearBuffer( Declare.GPS_DATA_BUFFER_TABLE_NAME );
        total += clearBuffer( Declare.PRIMITIVE_ACTIVITY_BUFFER_TABLE_NAME );
        
        // clear secondary tables
        total += clearBuffer( Declare.ADDRESS_BUFFER_TABLE_NAME );
        total += clearBuffer( Declare.PLACES_BUFFER_TABLE_NAME );
        total += clearBuffer( Declare.PLACES_TYPES_BUFFER_TABLE_NAME );
        total += clearBuffer( Declare.ACT_GPS_PLACE_BUFFER_TABLE_NAME );
        
        return total;
    }
    
    public static int clearBuffer(String tableName){
        int total = 0;
                
        try {
            String sqlStr = "";
            Statement stm = Declare.jdbcCon.createStatement();            
            
            // delete primary tables
            // delete table d_gpsData_buf
            if ( tableName.equalsIgnoreCase(Declare.GPS_DATA_BUFFER_TABLE_NAME) ) {
                sqlStr = "DELETE FROM " + Declare.GPS_DATA_BUFFER_TABLE_NAME + ";";
                total = stm.executeUpdate(sqlStr);
            }
            
            // delete table d_primActivities_buf
            if ( tableName.equalsIgnoreCase(Declare.PRIMITIVE_ACTIVITY_BUFFER_TABLE_NAME) ) {
                sqlStr = "DELETE FROM " + Declare.PRIMITIVE_ACTIVITY_BUFFER_TABLE_NAME + ";";
                total = stm.executeUpdate(sqlStr);
            }

            // delete table d_buf_address
            if ( tableName.equalsIgnoreCase(Declare.ADDRESS_BUFFER_TABLE_NAME) ) {
                sqlStr = "DELETE FROM " + Declare.ADDRESS_BUFFER_TABLE_NAME + ";";
                total = stm.executeUpdate(sqlStr);
            }
            
            if ( tableName.equalsIgnoreCase(Declare.PLACES_BUFFER_TABLE_NAME) ) {
                sqlStr = "DELETE FROM " + Declare.PLACES_BUFFER_TABLE_NAME + ";";
                total = stm.executeUpdate(sqlStr);
            }

            if ( tableName.equalsIgnoreCase(Declare.PLACES_TYPES_BUFFER_TABLE_NAME) ) {
                sqlStr = "DELETE FROM " + Declare.PLACES_TYPES_BUFFER_TABLE_NAME + ";";
                total = stm.executeUpdate(sqlStr);
            }
            
            if ( tableName.equalsIgnoreCase(Declare.ACT_GPS_PLACE_BUFFER_TABLE_NAME) ) {
                sqlStr = "DELETE FROM " + Declare.ACT_GPS_PLACE_BUFFER_TABLE_NAME + ";";
                total = stm.executeUpdate(sqlStr);
            }
            
            stm.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(DSMS.class.getName()).log(Level.SEVERE, null, ex);
        }
        return total;
    }

}
